const sql = require('mssql');
const baseFn = require('../../public/base.js');
const fs = require('fs'); // 图片路径
let uploadDir = './images/';
// 存储图片
const storagePic = (file) => {
    let fileName = file.name;
    let newFileName = Date.now() + '_' + fileName;
    let readStream = fs.createReadStream(file.path);
    let writeStream = fs.createWriteStream(uploadDir + newFileName);
    readStream.pipe(writeStream);
    return 'http://www.rsimg.com/' + newFileName
}

// 获取资产分类下拉选项的信息
const getSelectAssetClassifyInfo = async (ctx, next) => {
    // const body = ctx.request.body;
    let selectSql = `SELECT * FROM  ZCFLGL WHERE status = 'T'`;
    let result = await sql.query(selectSql);
    ctx.body = { result: result.recordset };
}
// 获取分类下面的多级名称
const getSelectAssetName = async (ctx, next) => {
    const body = ctx.request.body;
    let selectNameSql = `SELECT id AS value,name AS label,type,pid,status,classifyId FROM  ZCMCGL WHERE status = 'T' AND classifyId = ${body.id} `;
    let nameResult = await sql.query(selectNameSql);
    ctx.body = { nameResult: nameResult.recordset };
}



// 添加资产分类信息
const addAssetInfo = async (ctx, next) => {
    const files = ctx.request.files;
    const body = ctx.request.body;
    const assetInfo = JSON.parse(body.addAsset);
    let result = '';
    // 添加资产
    if (assetInfo.action === 'add') {
        let invoiceUrl = (0 in Object.keys(files)) ? storagePic(files.invoiceFile) : '';
        let actionSql = `INSERT INTO ZCGL 
    (brand,material,norm,unit,outNum,makeBusiness,purpose,config,classify,type,shopStatus,keepTime,buyTime,repairTime,num,price,buyPeople,businessName,connectName,connectPhone,assetSource,invoiceUrl,registerTime,keepStyle,assetName,model)
     VALUES ('${assetInfo.brand}','${assetInfo.material}','${assetInfo.norm}','${assetInfo.unit}','${assetInfo.outNum}','${assetInfo.makeBusiness}','${assetInfo.purpose}','${assetInfo.config}',${assetInfo.classify},${assetInfo.type},${assetInfo.shopStatus},'${assetInfo.keepTime}','${assetInfo.buyTime}','${assetInfo.repairTime}','${assetInfo.num}','${assetInfo.price}','${assetInfo.buyPeople}','${assetInfo.businessName}','${assetInfo.connectName}','${assetInfo.connectPhone}',${assetInfo.assetSource},'${invoiceUrl}','${baseFn.getNowFormatDate()}',${assetInfo.keepStyle},'${assetInfo.assetName.join(',')}','${assetInfo.model}')`;

        result = await sql.query(actionSql);
        let idResult = await sql.query(`select @@IDENTITY AS ID`);
        let id = idResult.recordset[0].ID;
        let configList = assetInfo.configList;
        if (0 in configList) {
            configList.forEach(async item => {
                let insertConfigSql = `INSERT INTO ZCPZXXGL(assetId,brand,normal,num,configId) VALUES(${id},'${item.brand}','${item.normal}',${item.num},${item.id}) `;
                await sql.query(insertConfigSql);
            });
        }
    } else {
        // 编辑资产
        let invoiceUrl = '';
        // 修改了图片
        if (0 in Object.keys(files)) {
            let picName = assetInfo.invoiceUrl.slice(assetInfo.invoiceUrl.lastIndexOf("\/"));
            fs.unlink(uploadDir + picName, () => { });
            invoiceUrl = storagePic(files.invoiceFile);
        }
        // 删除和插入资产配置的信息
        if (0 in assetInfo.configList) {
            assetInfo.configList.forEach(async item => {
                await sql.query(`DELETE FROM ZCPZXXGL WHERE assetId = ${item.assetId}`);
                let insertConfigSql = `INSERT INTO ZCPZXXGL(assetId,brand,normal,num,configId) VALUES(${item.assetId},'${item.brand}','${item.normal}',${item.num},${item.id}) `;
                await sql.query(insertConfigSql);
            });
        }
        let updateAssetInfoSql = `UPDATE ZCGL SET brand = '${assetInfo.brand}',material = '${assetInfo.material}',norm = '${assetInfo.norm}',unit = '${assetInfo.unit}',outNum = '${assetInfo.outNum}',makeBusiness = '${assetInfo.makeBusiness}',purpose = '${assetInfo.purpose}',config = '${assetInfo.config}',classify = ${assetInfo.classify},type = ${assetInfo.type},shopStatus = ${assetInfo.shopStatus},keepTime = '${assetInfo.keepTime}',buyTime = '${assetInfo.buyTime}',repairTime = '${assetInfo.repairTime}',num = '${assetInfo.num}',price = '${assetInfo.price}',buyPeople = '${assetInfo.buyPeople}',businessName = '${assetInfo.businessName}',connectName = '${assetInfo.connectName}',connectPhone = '${assetInfo.connectPhone}',assetSource = ${assetInfo.assetSource},registerTime = '${assetInfo.registerTime}',keepStyle = ${assetInfo.keepStyle},assetName = '${assetInfo.assetName}',model = '${assetInfo.model}'`;
        if (invoiceUrl) {
            updateAssetInfoSql += `,invoiceUrl = '${invoiceUrl}'`;
        }
        updateAssetInfoSql += ` WHERE id = ${assetInfo.id} `;
        result = await sql.query(updateAssetInfoSql);
    }

    if (result.rowsAffected[0] > 0) {
        ctx.body = {
            success: true
        }
    } else {
        ctx.body = {
            success: false
        }
    }
}
// 获取所有资产信息
const getAssetInfo = async (ctx, next) => {
    let getBody = ctx.request.body;
    let searchList = getBody.searchList;
    let totalCountSql = `SELECT COUNT(D.id) AS TOTAL FROM ZCGL D `;
    let selectSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY D.id) AS PX,* FROM ZCGL D  `;
    if (searchList.classify) {
        selectSql += ` WHERE D.classify = '${searchList.classify}'  `;
        totalCountSql += ` WHERE D.classify = '${searchList.classify}' `;
    }
    if (0 in searchList.assetName) {
        selectSql += selectSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        selectSql += ` D.assetName LIKE '%${searchList.assetName.join(',')}%' `;
        totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        totalCountSql += ` D.assetName LIKE '%${searchList.assetName.join(',')}%' `;
    }
    // if (searchList.idNum) {
    //     selectEntrySql += selectEntrySql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
    //     selectEntrySql += ` D.idnum LIKE '%${searchList.idNum}%' `;
    //     totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
    //     totalCountSql += ` D.idnum LIKE '%${searchList.idNum}%' `;
    // }
    // if (searchList.entryTime) {
    //     selectEntrySql += selectEntrySql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
    //     selectEntrySql += ` D.entrytime >= '${searchList.entryTime[0]}' AND D.entrytime <= '${searchList.entryTime[1]}' `;
    //     totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
    //     totalCountSql += ` D.entrytime >= '${searchList.entryTime[0]}' AND D.entrytime <= '${searchList.entryTime[1]}' `;
    // }
    selectSql += `) AS T1 WHERE T1.PX BETWEEN ${(getBody.pageIndex - 1) * (getBody.pageSize) + 1} AND ${getBody.pageIndex * (getBody.pageSize)} `;
    let result = await sql.query(selectSql);
    let total = await sql.query(totalCountSql);
    for (let item of result.recordset) {
        let configList = await sql.query(`SELECT P.assetId,P.brand,P.normal,P.num,P.configId AS id,M.name,M.status FROM ZCPZXXGL P LEFT JOIN ZCPZGL M ON P.configId = M.id WHERE assetId = ${item.id}`);
        item.configList = configList.recordset;
        let partResult = await sql.query(`SELECT STUFF((SELECT '-'+name FROM ZCMCGL WHERE id in (${item.assetName}) FOR XML PATH('')),1,1,'') AS name`);
        item.name = partResult.recordset[0].name;
    };
    ctx.body = { result: result.recordset, total: total.recordset[0].TOTAL };
}
// 获取分类的一级名称的信息
const getFirstLevelInfo = async (ctx, next) => {
    const body = ctx.request.body;
    let selectSql = `SELECT * FROM  ZCMCGL WHERE classifyId = ${body.id} AND type = '1'`;
    let result = await sql.query(selectSql);
    ctx.body = { result: result.recordset };
}
// 获取所有员工的部门信息
const getAllStaffInfo = async (ctx, next) => {
    let result = await sql.query(`SELECT Y.id,Y.username,Y.part FROM  YG_DA Y `);
    for (let item of result.recordset) {

        let partResult = await sql.query(`SELECT STUFF((SELECT '-'+name FROM GSGL WHERE id in (${item.part}) FOR XML PATH('')),1,1,'') AS name`);
        item.name = partResult.recordset[0].name;
    };
    ctx.body = { result: result.recordset };
}
// 资产申领
const addGetAsset = async (ctx, next) => {
    const body = ctx.request.body;
    let insertSql = `INSERT INTO ZCSL(assetid,SL,userid,applyTime,returnTime,BZ,status) `;
    body.assetList.forEach(async (item, ind) => {
        insertSql += ` SELECT ${item.id},${item.SL},${body.applyPeople},'${body.applyTime}','','${item.BZ}','F'`;
        if (ind !== body.assetList.length - 1) {
            insertSql += ` UNION `;
        }
        // 申领资产后减去可用资产的数量
        let updateSql = `UPDATE ZCGL SET num = ${item.ZCSL - item.SL} WHERE id = ${item.id}`;
        await sql.query(updateSql);
    });
    let result = await sql.query(insertSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = {
            success: true
        }
    } else {
        ctx.body = {
            success: false
        }
    }
}

// 资产报损
const harmGetAsset = async (ctx, next) => {
    const body = ctx.request.body;
    let insertSql = `INSERT INTO ZCBS(assetid,SL,userid,harmTime,BZ) `;
    body.assetList.forEach(async (item, ind) => {
        insertSql += ` SELECT ${item.id},${item.SL},${body.applyPeople},'${body.applyTime}','${item.BZ}'`;
        if (ind !== body.assetList.length - 1) {
            insertSql += ` UNION `;
        }
        // 报损资产后减去可用资产的数量
        let updateSql = `UPDATE ZCGL SET num = ${item.ZCSL - item.SL} WHERE id = ${item.id}`;
        await sql.query(updateSql);
    });
    let result = await sql.query(insertSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = {
            success: true
        }
    } else {
        ctx.body = {
            success: false
        }
    }
}


module.exports = {
    'GET /getSelectAssetClassifyInfo': getSelectAssetClassifyInfo,
    'POST /addAssetInfo': addAssetInfo,
    'POST /getAssetInfo': getAssetInfo,
    'POST /getSelectAssetName': getSelectAssetName,
    'POST /getFirstLevelInfo': getFirstLevelInfo,
    'GET /getAllStaffInfo': getAllStaffInfo,
    'POST /addGetAsset': addGetAsset,
    'POST /harmGetAsset': harmGetAsset,

    // 'POST /changeAssetClassifyStauts': changeAssetClassifyStauts,
}